package org.eclipse.epsilon.emc.rdbms.core.imodel;

import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collection;
import java.util.HashMap;
import java.util.Iterator;
import java.util.Map;
import java.util.Map.Entry;

import org.eclipse.emc.epsilon.rdbms.core.typemapper.TypeMapper;
import org.eclipse.epsilon.emc.rdbms.core.customexceptions.CustomException;
import org.eclipse.epsilon.emc.rdbms.core.imodeltransactionsupport.imodelTransactionSupport;
import org.eclipse.epsilon.emc.rdbms.core.persistancebroker.PersistanceBroker;
import org.eclipse.epsilon.emc.rdbms.core.persistancemechanism.PersistanceMechanism;
import org.eclipse.epsilon.emc.rdbms.core.persistancemechanism.RelationalDatabase;
import org.eclipse.epsilon.emc.rdbms.core.persistanceobject.PersistanceObject;
import org.eclipse.epsilon.eol.exceptions.EolRuntimeException;
import org.eclipse.epsilon.eol.exceptions.models.EolEnumerationValueNotFoundException;
import org.eclipse.epsilon.eol.exceptions.models.EolModelElementTypeNotFoundException;
import org.eclipse.epsilon.eol.exceptions.models.EolModelLoadingException;
import org.eclipse.epsilon.eol.exceptions.models.EolNotInstantiableModelElementTypeException;
import org.eclipse.epsilon.eol.execute.introspection.AbstractPropertyGetter;
import org.eclipse.epsilon.eol.execute.introspection.AbstractPropertySetter;
import org.eclipse.epsilon.eol.execute.introspection.IPropertyGetter;
import org.eclipse.epsilon.eol.execute.introspection.IPropertySetter;
import org.eclipse.epsilon.eol.models.Model;
import org.eclipse.epsilon.eol.types.EolModelElementType;
public class DBReflexiveModel extends Model 
{	
	PersistanceObject persistance_task = new PersistanceObject();
    boolean debug = true;
	/**
	* This  is not implemented
	* In future this may be used to load all relevant schema into the ClassMap at application launch preventing the need
	* for the database queries to get the primaryKey Names etc.
	*/

    public void executeSQL(String statement)
    {
    	imodelTransactionSupport transaction = new imodelTransactionSupport();
    	if(debug==true){System.out.println("ExecuteSQL");}
		try 
		{
			transaction.startTransaction();
			PersistanceBroker.processSql(statement);
			transaction.commitTransaction();
		} 
		catch (SQLException e)
		{
			transaction.rollbackTransaction();
		   e.printStackTrace();
		}
    }
    
    @Override
	public void load() throws EolModelLoadingException
	{
		if(debug==true){System.out.println("load");}
		//This may be required in the future in the class map hierachy as we may load all tables and column information on startup. This means that the application will
		//only need to query the database to update and retrieve data. This is a possible future improvement for performance however doing this will mean that if any tables
		//are created after startup then the application will not be able to reference them. e.g a user creates a table to merge data from subtables to c	
	}

	/**
	* This  is not implemented
	* @return returns null
	*/
	@Override
	public Object getEnumerationValue(String enumeration, String label) throws EolEnumerationValueNotFoundException 
	{
		if(debug==true){System.out.println("getEnumerationValue");}
		return null;
	}

	/**
	* This  is not implemented
	* @return returns null
	*/
	@Override
	public Collection<?> allContents() 
	{	
		if(debug==true){System.out.println("allContetnts");}
		return null;
	}
	
	/**
	* This  gets all the elements of a particular type e.g SELECT * FROM tablename
	* @param type This is the name of the type\ table to get the elements from.
	* @return elementList a list of all elements of the particular type.
	*/
	@Override
	public Collection<?> getAllOfType(String type) throws EolModelElementTypeNotFoundException 
	{
		if(debug==true){System.out.println("getAllOfType");}
		//Dont need to wrap a select in a transaction as we are not manipulating any data here.
		ArrayList<DBReflexiveModelElement> elementList = new ArrayList<DBReflexiveModelElement>(); 
		ArrayList<String> arrPrimaryKeyNames = new ArrayList<String>();
		try 
		{
			ResultSet AllOfType = persistance_task.getAllOfType(type, null, null);
			ResultSet PrimaryKeyNames = persistance_task.getKeys(type);
			
			while(PrimaryKeyNames.next())
			{
				arrPrimaryKeyNames.add(PrimaryKeyNames.getString(4));
			}
			int columnCount = AllOfType.getMetaData().getColumnCount();
			while(AllOfType.next())
			{

				DBReflexiveModelElement element = new DBReflexiveModelElement(type); 		
				element.setOwner(this);   
				for(int i=1;i<columnCount;i++)
				{
					if(arrPrimaryKeyNames.contains(AllOfType.getMetaData().getColumnName(i))==true)
					{  
						element.put(AllOfType.getMetaData().getColumnName(i), AllOfType.getString(i));	
					}
				}
				elementList.add(element);
			}	 
		} 
		catch (SQLException e) 
		{
			new CustomException("ERROR: DBReflexiveModel - Unable to retrieve data for type "+type+" "+e.getMessage());
		}
		//printresult("Result from getAllOfType", tmpResult); //to be deleted
		return elementList;   //We need to return a type of DBReflexiveModelElement but that doesnt contain any data so how is this a println() function?.
	}
//DBReflexiveModelElement
	/**
	* This  is calles the getAllOfTypeMethod
	* @param type This is the type\tablename that you would like to get all the elements from.
	* @return a list of all elements.
	*/
	@Override
	public Collection<?> getAllOfKind(String type) throws EolModelElementTypeNotFoundException 
	{
		if(debug==true){System.out.println("getAllOfKind");}
		return getAllOfType(type);        		
	}

	/**
	* This method gets the type of a particular Object 
	* @param instance  This is an object and can be any object but typically will be either a DBReflexiveModelElement or an EOLElementType
	* @return  the class of the Object
	*/
	@Override
	public Object getTypeOf(Object instance) 
	{
		if(debug==true){System.out.println("getTypeOf");}
		return instance.getClass();
	}
	
	
	/**
	* This method gets the owning type for a particular element.
	* @param instance  This is a DBReflxiveModelElement
	* @return  the type of the owning  Object as a string.
	*/
	@Override
	public String getTypeNameOf(Object instance) 
	{
		if(debug==true){System.out.println("getTypeNameOf");}
		return ((DBReflexiveModelElement) instance).getType();  //This gets the table name for the instance of the Model Element.
	}

	/**
	* This method is called when we want to create a new Model Element and add it to the database. 
	* @param type This is the type\tablename of the owing object for example calling this method will create a new row in a table called <type>
	* @return  This returns the new element that has been created.
	*/
	@Override
	public Object createInstance(String type) throws EolModelElementTypeNotFoundException, EolNotInstantiableModelElementTypeException
	{
		if(debug==true){System.out.println("CreateInstance");}
		imodelTransactionSupport transaction = new imodelTransactionSupport();
		try 
		{
			if(type !=null)
			{
			DBReflexiveModelElement element = new DBReflexiveModelElement(type);
			transaction.startTransaction();
			PersistanceObject  po = new PersistanceObject();
			persistance_task.addNewElement(element, null);		
			ResultSet result = transaction.processTransaction();
			
			element = PersistanceBroker.createElement(element, po.getKeys(type),result);
			element.setOwner(this);
			transaction.commitTransaction();
			return element;
			}
		} 
		catch (SQLException e) 
		{
			new CustomException("ERROR: DBReflexiveModel - Unable to create element "+ e.getMessage());
			transaction.rollbackTransaction();
		}
		return null;  //returns the element which has just been added to the database.
	}

	/**
	* This is not implemented
	* @return null
	*/
	@Override
	public Object getElementById(String id) 
	{
		if(debug==true){System.out.println("getElementbyId");}
		//Since we no longer have the list of DBReflexiveModelElements how can we tell which table this id comes from?
		return null;
	} 

	/**
	* This is not implemented
	* @return null
	*/
	@Override
	public String getElementId(Object instance) 
	{
		if(debug==true){System.out.println("getElementId");}
		//This should not return a string. There really is no point to this method as we could simply call Element.getProps() to get the PK for the element.
        //All this does is pointless formatting that will never be used.
		String primaryKeyValue=null;  //Set the primary Key Value to be null
		Iterator<Entry<String, Object>> iterator = ((DBReflexiveModelElement) instance).getProps().entrySet().iterator(); 
		HashMap<String, Object> map = ((DBReflexiveModelElement) instance).getProps();  //Get the Hash Map Values for the Primary Key. 

		while (iterator.hasNext()) 
		{ 
			Map.Entry pairs = iterator.next(); 
			primaryKeyValue = pairs.getValue().toString();
			//iterator.remove();  avoids a ConcurrentModificationException
			//If we have a composite PrimaryKey Value then this concatenates the values together.
			if(iterator.hasNext()==true)  
			{
				primaryKeyValue = primaryKeyValue+'_';
			}		        	
		} 
		return primaryKeyValue;
	}

	/**
	* This is not implemented
	* @return null
	*/
	@Override
	//This should not be implemented as we must use the PK assignment from the database so you are not allowed to set the PK_Value in the model.
	public void setElementId(Object instance, String newId) 
	{
		if(debug==true){System.out.println("setElementId");}
	}

	/**
	* This is called when we wish to delete an object from the database and from EOL. This deletes the row from the database and sets the object owner to null;
	* @param instance. This is the element or type to be deleted specifying a type will delete all elements of this type.
	*/
	@Override
	//Delete Row from Database.
	public void deleteElement(Object instance) throws EolRuntimeException 
	{
		if(debug==true){System.out.println("deleteElement");}
		imodelTransactionSupport transaction = new imodelTransactionSupport();
		//We have to delete the element from the database and then remove the element from the Model by Setting the Owner to null.
		try 
		{
			transaction.startTransaction();
			if((instance.getClass().getSimpleName().toString()).equals("DBReflexiveModelElement")==true)
			{
				//PersistanceObject deleteElement = new PersistanceObject();	
				persistance_task.deleteElement(((DBReflexiveModelElement) instance));
				((DBReflexiveModelElement) instance).setOwner(null); //This sets the owner to null so we know that it no longer belongs in the Model.
			}
			else if ((instance.getClass().getSimpleName().toString()).equals("EolModelElementType")==true)
			{
				Collection<?> elements = ((EolModelElementType) instance).getAllInstances();
				Iterator itr = elements.iterator();
				int i=0;
				while(itr.hasNext()) 
				{
					Object element = itr.next();
					((DBReflexiveModelElement) element).setOwner(null);
					//persistance_task.deleteElement(((DBReflexiveModelElement) element)); //Now we could simply do this which would issue a delete command for each element which may be rather slow.
				}
				//persistance_task.deleteElement(((DBReflexiveModelElement) element)); //Now we could simply do this which would issue a delete command for each element which may be rather slow.
				persistance_task.deleteType(((EolModelElementType) instance).getName());
			}   
			transaction.processTransaction();
			transaction.commitTransaction();
		} 
		catch (SQLException e) 
		{	
			transaction.rollbackTransaction();
			e.printStackTrace();
		}
	}

	/**
	 * This checks to see if the object specified is owned by the model only tables which have primary keys specified can be added to the model.
	 * If an element is specified it checks to see if the element type is a table in the model.
	* @param instance this is either the table or element that needs to be verified.
	* @return boolean Returns true or false depending on if the instance is instantiable and is owned by the model.
	*/
	@Override
	public boolean owns(Object instance) 
	{
		if(debug==true){System.out.println("owns");}
		//Now we need to check for the elements and if all the elements are in the model.
		if(instance !=null)
		{
			if((instance.getClass().getSimpleName().toString()).equals("DBReflexiveModelElement")==true)
			{
				if(((DBReflexiveModelElement) instance).getOwner()==this)
				{
					return true;
				}
			}
		}
		return false;
	}

	/**
	 * This checks to see if the object is instantiable and calls the hastype method. 
	* @param type This is the type\tablename for the object.
	* @return boolean Returns true or false depending on if the instance is instantiable and is owned by the model.
	*/
	@Override
	//returns true look at many to many relationships here...
	public boolean isInstantiable(String type)
	{	
		if(debug==true){System.out.println("isInstantiable");}
		return hasType(type);
	}

	/**
	 * This checks to see if the object is a model element. This is now superceeded by owns.
	 * 
	* @param instance This is the type\tablename for the object.
	* @return boolean Returns true or false depending on if the instance is instantiable and is owned by the model.
	*/
	@Override
	//Checks to see if it is a DBReflexiveModelElement owns supersedes this..
	public boolean isModelElement(Object instance) 
	{
		if(debug==true){System.out.println("isModelElement");}
		return owns(instance);
	}

	/**
	 * This checks to see if the object is instantiable. 
	 * 
	* @param type This is the type\tablename for the object.
	* @return boolean Returns true or false depending on if the instance is instantiable and is owned by the model.
	*/
	@Override
	//If there is a table of that name in the model if there is then it returns true of it does not it returns false.
	public boolean hasType(String type) 
	{
		if(debug==true){System.out.println("hasType");}
		//Check that the type exists in the database and has a primary Key associated with it.		
		//PersistanceObject hasType = new PersistanceObject();
		if(type !=null)
		{
		try 
		{
			if(persistance_task.getKeys(type).next()==true)
			{
				return true;
			}
			else
			{	
			return false;
			}
		} 
		catch (SQLException e) 
		{
			new CustomException("ERROR: DBReflexiveModel - Cannot verify if the model has the type "+type);
			return false;
		}
		}
		return false;
	}   

	/**
	 * This is not implemented
	 * 
	*/
	@Override
	//This does not need to do anything as we are not creating tables in the datababase although that could be one for the future?
	public boolean store(String location) 
	{
		if(debug==true){System.out.println("store1");}
		return false;
	}

	/**
	 * This is not implemented
	 * 
	*/	
	@Override
	//This does not need to do anything as we are not creating tables in the datababase although that could be one for the future?
	public boolean store() 
	{
		if(debug==true){System.out.println("store2");}
		return false;
	}
	
	public void printresult(String Title, ResultSet resultset)
	{
		PersistanceBroker pb = new PersistanceBroker();
		pb.connectTo().printResultSet(Title, resultset);	
	}
	
	

	/**
	 * This returns an instance of the IPropertyGetter class
	 * @return ElementPropertyGetter
	*/
	@Override
	//returns implementation of iPropertyGetter.
	public IPropertyGetter getPropertyGetter() 
	{
		if(debug==true){System.out.println("getPropertyGetter");}
		return new ElementPropertyGetter();
	}

	/**
	 * This returns an instance of the IPropertyGetter class
	 * @return ElementPropertySetter
	*/
	@Override
	//Setting the value for the Object.
	public IPropertySetter getPropertySetter() 
	{
		if(debug==true){System.out.println("GetPropertySetter");}
		return new ElementPropertySetter();
	}

	
	class ElementPropertyGetter extends AbstractPropertyGetter 
	{
		/**
		 * This returns the value of a specified property from the database.
		 * @param object The element that will be retrieved from the database
		 * @param property The column that will be retrieved from the database
		 * @return value from the database
		*/
		@Override
		//Gets Model Element and Column Name.
		public Object invoke(Object object, String property) throws EolRuntimeException 
		{
			Object resultelement = new DBReflexiveModelElement(((DBReflexiveModelElement) object).getType());
			ArrayList<String> columnNames = new ArrayList<String>();
			columnNames.add(property);
			String  propertycolumnName = null;
			if(debug==true){System.out.println("Invoke ElementPropertyGetter");}
			Object value = null;
			ResultSet  result = null;
			ResultSet tmpResult = null;
			Object element = null; 
			//Check that the property is a column in the object table.
			{
		    	try 
				{
					boolean isSQLColumn=false;
					boolean isSQLFK=false;
					String FKTableName = null;
					if(property !=null) 
					{
						//Need to check if the name of the property is the name of a column in the table.
						ResultSet SQLColumns = persistance_task.getSQLColumns(((DBReflexiveModelElement) object));									
						System.out.println(((DBReflexiveModelElement) object).getType());
						while (SQLColumns.next())
						{
							if(SQLColumns.getString("COLUMN_NAME").toUpperCase().equals(property.toUpperCase()))
							{
								isSQLColumn=true;
							}
						}
						if(isSQLColumn==false)
						{
							//We need to check if this is a foreign Key relationship
							ResultSet FKInfoExported =  persistance_task.getFKColumnsExported(((DBReflexiveModelElement) object));	
							while (FKInfoExported.next())
							{
								if(FKInfoExported.getString("FK_NAME").toUpperCase().equals(property.toUpperCase()))
								{
									System.out.println(FKInfoExported.getString(7));
									FKTableName=FKInfoExported.getString(7);
									//We know that the property specified is that of a foreign Key Name;	
									isSQLFK=true;
								}
							}
							if(isSQLFK==false)
							{
								//We know that there were no exported keys found so we need to try the imported keys
								ResultSet FKInfoImported =  persistance_task.getFKColumnsImported(((DBReflexiveModelElement) object));
								while (FKInfoImported.next())
								{
									if(FKInfoImported.getString("FK_NAME").toUpperCase().equals(property.toUpperCase()))
									{
										FKTableName = FKInfoImported.getString(3);
										//We know that the property specified is that of a foreign Key Name;	
										isSQLFK=true;
									}
							}
						   }
						}
						if(object.getClass().getSimpleName().toString().equals("DBReflexiveModelElement")==true && isSQLFK==true)
						{
							if(property!=null)
							{
							//Now we know that there is a foreign key relationship specified we need to return the necessary results and check the cardinality of the result set.
							result = persistance_task.getElement(((DBReflexiveModelElement) object), columnNames);
							ResultSet resultcnt = persistance_task.getElement(((DBReflexiveModelElement) object), columnNames);
							resultcnt.last();
							if(result!=null)
							{
							  if(resultcnt.getRow()==0)
							{
								return null; //we dont want to return anything at present here.
							}
							else if(resultcnt.getRow()==1)
							 {
                               //We need to know what the Foreign Key tablename was that we need to join onto.
								while (result.next())
							   {
							   //We need to return a DBReflexiveModelElement.
                               DBReflexiveModelElement addelement = new DBReflexiveModelElement(FKTableName);
                               //We need to set the owning model.
                               addelement.setOwner(DBReflexiveModel.this);
                               //Now we need to add the Primary Key Values for the element.
                               addelement.put(result.getMetaData().getColumnLabel(1),result.getString(1));
                               return addelement;
								}
							 }
							else if(resultcnt.getRow()>1)
							{
								//We need to return a collection of DBRefexiveModelElements.
								ArrayList<DBReflexiveModelElement> elementList = new ArrayList<DBReflexiveModelElement>();
								while (result.next())
								{
									 //We need to return a DBReflexiveModelElement.
		                               DBReflexiveModelElement addelement = new DBReflexiveModelElement(FKTableName);
		                               //We need to set the owning model.
		                               addelement.setOwner(DBReflexiveModel.this);
		                               //Now we need to add the Primary Key Values for the element.
		                               addelement.put(result.getMetaData().getColumnLabel(1),result.getString(1));
		                               //Now we need to add the element to the element ArrayList
		                               elementList.add(addelement);
								}
								return elementList;	
							}
							}
							else
							{	
								return null; //if the result set is null then we need to return a null object.
							}
							}
						}	
					if(object.getClass().getSimpleName().toString().equals("DBReflexiveModelElement")==true && isSQLColumn==true)
					{
						if(property!=null )
						{
						result = persistance_task.getElement(((DBReflexiveModelElement) object), columnNames);
						ResultSet resultcnt = persistance_task.getElement(((DBReflexiveModelElement) object), columnNames);
						if(result!=null)
						{
							resultcnt.last();
						if(resultcnt.getRow()==1)
						 {
							//We know that only one row has been returned therefore we can simply return the result from the resultset.
							ResultSetMetaData rsmd = result.getMetaData();
							Object dataType = new TypeMapper().getJavaFromSQLDataType(rsmd.getColumnTypeName(1).toString());							
						//We need to check how many rows that the persistance task has returned.
						while (result.next())
                        {
							
							if(dataType.toString().equals("STRING"))
							{
							return result.getString(1);	
							}
							else if(dataType.toString().equals("INTEGER"))
							{
								System.out.println(result.getString(1));
								return result.getInt(1);
							}
							else if(dataType.toString().equals("BOOLEAN"))
							{
								return	result.getBoolean(1);
							}
							else if(dataType.toString().equals("TIME"))
							{
								return result.getTime(1);
							}
							else if(dataType.toString().equals("DOUBLE"))
							{
								return result.getDouble(1);
							}
                        }
                        }
						else if(resultcnt.getRow()>1)
						{
						//Multiple Rows have been returned therefore we need to return a collection of objects.
							ArrayList<DBReflexiveModelElement> elementList = new ArrayList<DBReflexiveModelElement>();
							while (result.next())
							{
							//We need to create a new object element here and add to the collection
								{
								DBReflexiveModelElement elementtoadd = new DBReflexiveModelElement(((DBReflexiveModelElement) object).getType());
								
								elementList.add(elementtoadd);
								}
							}
							return elementList;
						}
						else if(resultcnt.getRow()==0)
						{
						   return null;
						}			
						}
					}
				  } 
				}
				}
				catch (SQLException e) 
				{
					new CustomException("ERROR - DBReflexiveModel - Unable to obtain get property");
					e.printStackTrace();
				}
			}
			return null;
		}
	}

	class ElementPropertySetter extends AbstractPropertySetter
	{
		//This is for an update to the database.
		/**
		 * This sets the value of a specified property in the database for a specified element.
		 * @value the value to be set.
		 * @return value from the database
		*/
		@Override
		public void invoke(Object value) throws EolRuntimeException 
		{		
			if(debug==true){System.out.println("Invoke ElementPropertySetter");}
			
			imodelTransactionSupport transaction = new imodelTransactionSupport();
			try 
			{
				transaction.startTransaction();
				HashMap<String, Object> properties = new HashMap<String, Object>();				
				if(value==null)
				{
					properties.put(property, null);	
					persistance_task.UpdateType(((EolModelElementType) object).getName(),properties);
				}
				else if(value.getClass().getSimpleName().equals("ArrayList")==true)
				{
					ArrayList<?> tmp = ((ArrayList<?>) value);
					for(int i=0;i< tmp.size();i++)
					{	
						String[] val = (String[]) tmp.get(0);
						properties.put(property, (val[i]));	
					}
				}
				else if(value.getClass().getSimpleName().equals("JdbcResultSet")==true)
				{	
					while(((ResultSet) value).next())
					{
						properties.put(((ResultSet) value).getMetaData().getColumnName(1), ((ResultSet) value).getString(1));
					}
					//persistance_task.updateElement(((DBReflexiveModelElement) object),properties);
				}
				else
				{	
					properties.put(property, value);
				}	
				if(object.getClass().getSimpleName().equals("EolModelElementType"))
				{
					persistance_task.UpdateType(((EolModelElementType) object).getName(),properties);
				}
				else if(object.getClass().getSimpleName().equals("DBReflexiveModelElement"))
				{
					persistance_task.updateElement(((DBReflexiveModelElement) object),properties);
				}
				transaction.processTransaction();
				transaction.commitTransaction();
			} 
			catch (SQLException e) 
			{
				transaction.rollbackTransaction();
				new CustomException("ERROR: - DBReflexiveModel - A Database error has occured: "+e.getMessage());
			}
		}
	}  
}	
	